❮

limits of max, min optimization

  • Jump to comment-1
    pavel.stehule@gmail.com2022-07-18T13:24:02+00:00
    Hi I am trying to fix one slow query, and found that optimization of min, max functions is possible only when there is no JOIN in the query. Is it true? I need to do manual transformation of query select max(insert_date) from foo join boo on foo.boo_id = boo.id where foo.item_id = 100 and boo.is_ok to select insert_date from foo join boo on foo.boo_id = boo.id where foo.item_id = 100 and boo.is_ok order by insert_date desc limit 1; Regards Pavel
    • Jump to comment-1
      alvherre@alvh.no-ip.org2022-07-18T14:24:03+00:00
      On 2022-Jul-18, Pavel Stehule wrote: > Hi > > I am trying to fix one slow query, and found that optimization of min, max > functions is possible only when there is no JOIN in the query. > > Is it true? See preprocess_minmax_aggregates() in src/backend/optimizer/plan/planagg.c > select max(insert_date) from foo join boo on foo.boo_id = boo.id > where foo.item_id = 100 and boo.is_ok Maybe it is possible to hack that code so that this case can be handled better. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
      • Jump to comment-1
        tgl@sss.pgh.pa.us2022-07-18T14:29:07+00:00
        Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > On 2022-Jul-18, Pavel Stehule wrote: >> I am trying to fix one slow query, and found that optimization of min, max >> functions is possible only when there is no JOIN in the query. > See preprocess_minmax_aggregates() in > src/backend/optimizer/plan/planagg.c > Maybe it is possible to hack that code so that this case can be handled > better. The comments show this was already thought about: * We also restrict the query to reference exactly one table, since join * conditions can't be handled reasonably. (We could perhaps handle a * query containing cartesian-product joins, but it hardly seems worth the * trouble.) regards, tom lane
        • Jump to comment-1
          pavel.stehule@gmail.com2022-07-18T14:32:56+00:00
          po 18. 7. 2022 v 16:29 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > > On 2022-Jul-18, Pavel Stehule wrote: > >> I am trying to fix one slow query, and found that optimization of min, > max > >> functions is possible only when there is no JOIN in the query. > > > See preprocess_minmax_aggregates() in > > src/backend/optimizer/plan/planagg.c > > Maybe it is possible to hack that code so that this case can be handled > > better. > > The comments show this was already thought about: > > * We also restrict the query to reference exactly one table, since > join > * conditions can't be handled reasonably. (We could perhaps handle a > * query containing cartesian-product joins, but it hardly seems worth > the > * trouble.) > > Thank you for reply Regards Pavel > regards, tom lane >